package cnedu.ncist.studenteffort.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import edu.ncist.wang.hkdf.bussiness.blo.AbstractService;
import edu.ncist.wang.hkdf.dao.database.DBConnectionException;
import edu.ncist.wang.hkdf.dao.database.DataBaseAccess;
import edu.ncist.wang.hkdf.dao.database.DataManipulationException;

public class StudenteffortService extends AbstractService {

	public Map<String, Object> doServiceProcess(Map paraMap){
		// TODO Auto-generated method stub
		String actionType = (String)paraMap.get("ACTIONTYPE");
		if("student_Is_Effort_Overall".equals(actionType)){
			return student_is_effort_overall(paraMap);
		}
		if("student_Is_Effort_Detail".equals(actionType)){
			return student_is_effort_detail(paraMap);
		}
		if("student_Is_Effort_Data".equals(actionType)){
			return student_is_effort_data(paraMap);
		}
		else{
			ActionTypeError();
		}
		return null;
	}
	
	
	
	public Map<String, Object> student_is_effort_overall(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		String choice1=map.get("jobId").toString();
		String studentName=map.get("studentName").toString().replace(" ", "");
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentIsEffortList = null;
		List<Map<String, String>> allJob = null;
		List<Map<String, String>> topicAmount = null;
		String sql=null;
		String topicAmountSql = null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1==""){
			sql = "select student_id,student_name,sum(temp.is_effort) as total_value   "+
						" from(   "+
						"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
						"  student_is_effort.job_id,student_is_effort.topic_id,   "+
						"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
						"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
						" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
						" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
						" from  "+
						" student_is_effort LEFT OUTER JOIN student_complete_status  "+
						" on student_is_effort.student_id=student_complete_status.student_id  "+
						" and student_is_effort.topic_id=student_complete_status.topic_id  "+
						"  left outer join job_is_hard   "+
						"  on student_complete_status.student_id=job_is_hard.student_id   "+
						"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
						"  left outer join student   "+
						"  on job_is_hard.student_id=student.student_id    "+
						"  where    "+
						"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
						 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
						"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ";
						
			topicAmountSql =  "select count(*) as topic_amount FROM topic  "+
												" where belong_course_id='"+courseId+"'  "+
												"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
		}
		else{
			sql ="select student_id,student_name,sum(temp.is_effort) as total_value   "+
					" from(   "+
					"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
					"  student_is_effort.job_id,student_is_effort.topic_id,   "+
					"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
					"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
					" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
					" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
					" from  "+
					" student_is_effort LEFT OUTER JOIN student_complete_status  "+
					" on student_is_effort.student_id=student_complete_status.student_id  "+
					" and student_is_effort.topic_id=student_complete_status.topic_id  "+
					"  left outer join job_is_hard   "+
					"  on student_complete_status.student_id=job_is_hard.student_id   "+
					"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
					"  left outer join student   "+
					"  on job_is_hard.student_id=student.student_id    "+
					"  where    "+
					"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
					 " and student_is_effort.job_id='"+choice1+"'  "+
					"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ";
			topicAmountSql =  "select count(*) as topic_amount FROM topic  "+
					" where belong_course_id='"+courseId+"'  "+
					"  and belong_job_id='"+choice1+"'   ";
		}
		if(!(studentName.equals("")) && studentName!=null){
			sql = sql+"  " +"     where temp.student_name like '%"+studentName+"%'  ";
		}
		sql =  sql + "     group by student_id     ";
		String  sql1 = "select job_id FROM student_is_effort where course_id='"+courseId+"' and class_id='"+classId+"' GROUP BY job_id";
		try{
			studentIsEffortList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			topicAmount =  dba.getMultiRecord(topicAmountSql);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null ==studentIsEffortList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTISEFFORT",  studentIsEffortList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("TOPICAMOUNT", topicAmount);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	
	public Map<String, Object> student_is_effort_detail(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String studentName=map.get("studentName").toString().replace(" ", "");
		String topicId=map.get("topicId").toString();
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentIsEffortDetailList = null;
		List<Map<String, String>> allJob = null;
		List<Map<String, String>> allTopic = null;
		String sql=null;
		String topicsql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1=="" ){
			sql = "  select student_name,student_id,course_id,job_id,topic_id,is_effort,topic_abstract   "+
					"  from     "+
					" (select student.student_name,student_is_effort.student_id,student_is_effort.course_id,  "+
					  "  student_is_effort.job_id,student_is_effort.topic_id,  "+
					   "  (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
					   "  student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort     "+
					   "  from   "+
					   "  student_is_effort LEFT OUTER JOIN student_complete_status   "+
					   "  on student_is_effort.student_id=student_complete_status.student_id   "+
					   "  and student_is_effort.topic_id=student_complete_status.topic_id   "+
					   "  left outer join job_is_hard   "+
					   "  on student_complete_status.student_id=job_is_hard.student_id   "+
					   "  and student_complete_status.topic_id=job_is_hard.topic_id   "+
					   "  left outer join student   "+
					   "  on job_is_hard.student_id=student.student_id    "+
					   "  where   "+
					   "  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
					   "  and student_is_effort.job_id=(select job_id from student_is_effort  where course_id='"+courseId+"'   and class_id='"+classId+"'   order by job_id desc  limit 1)   "+
					   " order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC   )  "+
					   "  temp,topic   "+
					   "  where temp.topic_id=topic.topic_only_id  ";
			 topicsql = "select DISTINCT topic_only_id,topic_abstract from topic   "+
					 			 "  where belong_course_id='"+courseId+"'   "+
					 			 "  and belong_job_id=(select job_id from student_is_effort	where course_id='"+courseId+"'   and class_id='"+classId+"'   	 order by job_id desc  limit 1)";
		}
		else{
			sql =  "  select student_name,student_id,course_id,job_id,topic_id,is_effort,topic_abstract   "+
					"  from     "+
					" (select student.student_name,student_is_effort.student_id,student_is_effort.course_id,  "+
					  "  student_is_effort.job_id,student_is_effort.topic_id,  "+
					   "  (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
					   "  student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort     "+
					   "  from   "+
					   "  student_is_effort LEFT OUTER JOIN student_complete_status   "+
					   "  on student_is_effort.student_id=student_complete_status.student_id   "+
					   "  and student_is_effort.topic_id=student_complete_status.topic_id   "+
					   "  left outer join job_is_hard   "+
					   "  on student_complete_status.student_id=job_is_hard.student_id   "+
					   "  and student_complete_status.topic_id=job_is_hard.topic_id   "+
					   "  left outer join student   "+
					   "  on job_is_hard.student_id=student.student_id    "+
					   "  where   "+
					   "  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
					   "  and student_is_effort.job_id='"+choice1+"'   "+
					   " order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC   )  "+
					   "  temp,topic   "+
					   "  where temp.topic_id=topic.topic_only_id  ";
			 topicsql = "select DISTINCT topic_only_id,topic_abstract from topic   "+
					 			 "  where belong_course_id='"+courseId+"'   "+
					 			 "  and belong_job_id='"+choice1+"'     ";
		}
		if(!(topicId.equals("")) && topicId!=null){
			sql=sql+"  "+" and temp.topic_id='"+topicId+"'  ";
		}
		if(!(studentName.equals("")) && studentName!=null){
			sql = sql+"  " +"     and temp.student_name like '%"+studentName+"%'   ";
		}
		String  sql1 = "select job_id FROM student_is_effort where course_id='"+courseId+"' and class_id='"+classId+"'  GROUP BY job_id";
		try{
			studentIsEffortDetailList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			allTopic = dba.getMultiRecord(topicsql);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null == studentIsEffortDetailList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTISEFFORTDETAIL",  studentIsEffortDetailList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("ALLTOPIC", allTopic);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}
	
	
	
	
	public Map<String, Object> student_is_effort_data(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		String choice1=map.get("jobId").toString();
		String studentName=map.get("studentName").toString().replace(" ", "");
		String topicId=map.get("topicId").toString();
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>>  studentIsEffortDataList = null;
		List<Map<String, String>> allJob = null;
		List<Map<String, String>> allTopic = null;
		String topicsql=null;
		String sql=null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		if(choice1==null || choice1==""){
			sql = "select student_name,student_id,course_id,job_id,topic_id,topic_abstract,is_many_times_to_submit,is_deadline,submit_statu,scorer_index   "+
					" from  "+
					" (select student.student_name,student_is_effort.student_id,student_is_effort.course_id,   "+
					" student_is_effort.job_id,student_is_effort.topic_id,   "+
					" student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
					" student_complete_status.submit_statu,job_is_hard.scorer_index   "+
					" from   "+
					" student_is_effort LEFT OUTER JOIN student_complete_status   "+
					" on student_is_effort.student_id=student_complete_status.student_id   "+
					" and student_is_effort.topic_id=student_complete_status.topic_id   "+
					" left outer join job_is_hard  "+
					" on student_complete_status.student_id=job_is_hard.student_id   "+
					" and student_complete_status.topic_id=job_is_hard.topic_id   "+
					" left outer join student   "+
					" on job_is_hard.student_id=student.student_id  "+
					" where   "+
					" student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'   "+
					" and student_is_effort.job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'    and class_id='"+classId+"'      order by job_id desc  limit 1)  "+
					" order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC)  "+
					" temp,topic  "+
					" where temp.topic_id=topic.topic_only_id ";
			 topicsql = "select DISTINCT topic_only_id,topic_abstract from topic   "+
		 			 "  where belong_course_id='"+courseId+"'   "+
		 			 "  and belong_job_id=(select job_id from student_is_effort	where course_id='"+courseId+"'   and class_id='"+classId+"'   	 order by job_id desc  limit 1)";
		}
		else{
			sql = "select student_name,student_id,course_id,job_id,topic_id,topic_abstract,is_many_times_to_submit,is_deadline,submit_statu,scorer_index   "+
					" from  "+
					" (select student.student_name,student_is_effort.student_id,student_is_effort.course_id,   "+
					" student_is_effort.job_id,student_is_effort.topic_id,   "+
					" student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
					" student_complete_status.submit_statu,job_is_hard.scorer_index   "+
					" from   "+
					" student_is_effort LEFT OUTER JOIN student_complete_status   "+
					" on student_is_effort.student_id=student_complete_status.student_id   "+
					" and student_is_effort.topic_id=student_complete_status.topic_id   "+
					" left outer join job_is_hard  "+
					" on student_complete_status.student_id=job_is_hard.student_id   "+
					" and student_complete_status.topic_id=job_is_hard.topic_id   "+
					" left outer join student   "+
					" on job_is_hard.student_id=student.student_id  "+
					" where   "+
					" student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'   "+
					" and student_is_effort.job_id='"+choice1+"'  "+
					" order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC)  "+
					" temp,topic  "+
					" where temp.topic_id=topic.topic_only_id ";
			 topicsql = "select DISTINCT topic_only_id,topic_abstract from topic   "+
		 			 "  where belong_course_id='"+courseId+"'   "+
		 			 "  and belong_job_id='"+choice1+"'     ";
		}
		if(!(topicId.equals("")) && topicId!=null){
			sql=sql+"  "+" and temp.topic_id='"+topicId+"'  ";
		}
		if(!(studentName.equals("")) && studentName!=null){
			sql = sql+"  " +"     and temp.student_name like '%"+studentName+"%'   ";
		}
		String  sql1 = "select job_id FROM student_is_effort where course_id='"+courseId+"' and class_id='"+classId+"'  GROUP BY job_id";
		try{
			studentIsEffortDataList = dba.getMultiRecord(sql);
			allJob = dba.getMultiRecord(sql1);
			allTopic = dba.getMultiRecord(topicsql);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get user info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		if(null == studentIsEffortDataList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		resultMap.put("STUDENTISEFFORTDATA", studentIsEffortDataList);
		resultMap.put("ALLJOB", allJob);
		resultMap.put("ALLTOPIC", allTopic);
		resultMap.put("CONDITIONMAP", map);
		return resultMap;
	}



}
